package trlygjj.tanruiliyigenjinjin.util;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import trlygjj.tanruiliyigenjinjin.domain.Employee;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

public class ExportExcelUtil {

    public static void createExcel(HttpServletResponse response, List pageDataList, String fileName){
        try {
            OutputStream out=response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;fileName="+new String((fileName+".xls").getBytes(),"utf-8"));
            OutputStream out1=new FileOutputStream("e:/test.xls");
            WritableWorkbook workbook= Workbook.createWorkbook(out);//建立excel文件
            WritableSheet wsheet=workbook.createSheet("导出数据",0);//sheet
            WritableCellFormat writableCellFormat=new WritableCellFormat();
            writableCellFormat.setAlignment(Alignment.RIGHT);//右对齐
            WritableFont wf=new WritableFont(WritableFont.ARIAL,24,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
            WritableCellFormat wcf=new WritableCellFormat(wf);//title单元格
            wcf.setAlignment(jxl.format.Alignment.CENTRE);//设置对齐方式
            wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
            WritableCellFormat wcfc=new WritableCellFormat();
            wcfc.setAlignment(jxl.format.Alignment.CENTRE);
            wcfc.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            WritableCellFormat wcfe=new WritableCellFormat();//一般单元格定义
            wcfe.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            wsheet.setColumnView(0,20);///设置列宽
            wsheet.setColumnView(1,15);
            wsheet.setColumnView(2,30);
            wsheet.setColumnView(3,30);
            wsheet.setColumnView(4,15);
            wsheet.setColumnView(5,40);
            wsheet.setColumnView(6,10);
            wsheet.setColumnView(7,10);
            int rowIndex=0;//行高
            int coulumIndex=0;//列高
            if(null!=pageDataList){
                coulumIndex=0;
                wsheet.setRowView(rowIndex, 500);//标题行高
                wsheet.addCell(new Label(coulumIndex++,rowIndex,fileName,wcf));
                wsheet.mergeCells(0,rowIndex, 5, rowIndex);
                rowIndex++;
                coulumIndex=0;
                wsheet.setRowView(rowIndex, 380);
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"账户名",wcfc));
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"职位",wcfc));
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"电话",wcfc));
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"邮箱",wcfc));
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"部门",wcfc));
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"入职时间",wcfc));
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"超级管理员",wcfc));
                wsheet.addCell(new Label(coulumIndex++,rowIndex,"状态",wcfc));

                //开始循环
                for (Object object : pageDataList) {
                    //Object[] array=(Object[])object;
                    Employee array= (Employee) object;
                    rowIndex++;
                    coulumIndex=0;
                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getUsername()==null?"":array.getUsername().toString(),wcfe));
                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getPositions()==null?"":array.getPositions().toString(),wcfe));
                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getTel()==null?"":array.getTel().toString(),wcfe));

                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getEmail()==null?"":array.getEmail().toString(),wcfe));
                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getDepartment()==null?"":array.getDepartment().toString(),wcfe));
                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getInputTime()==null?"":array.getInputTime().toString(),wcfe));
                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getAdmin()==0?"":String.valueOf(array.getAdmin()),wcfe));
                    wsheet.addCell(new Label(coulumIndex++,rowIndex,array.getState()==0?"":String.valueOf(array.getState()),wcfe));
                }
            }
            workbook.write();
            if(workbook!=null){
                workbook.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
